package com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.repository.impl;

import com.css.fxfzxczh.modules.lossAssessment.houseCategoryArea.entity.HouseCategoryArea;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.entity.GovOffInfo;
import com.css.fxfzxczh.modules.lossReportManage.lossReportEscalation.repository.GovOffInfoRepository;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Repository
public class GovOffInfoRepositoryImpl implements GovOffInfoRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void deleteByXlrmId(String xlrmId) {
        String sql = "delete from xczh_government_office_info  where xlrm_id= '"+xlrmId+"'" ;
        jdbcTemplate.update(sql);
    }

    @Override
    public void save(GovOffInfo govOffInfo) {
        String sql = "insert into xczh_government_office_info(xgoi_id,province,city,county,unit_name,affiliated_unit,purpose," +
                "indoor_asset_avg,unit_price1,tel,fill_form_user,fill_form_time,unit_price2,unit_price31,unit_price32," +
                "unit_price4,unit_price51,unit_price52,unit_price53,unit_price6,unit_price7,area1,area2,area31,area32," +
                "area4,area51,area52,area53,area6,area7,xlrm_id) " +
                "values(:xgoiId, :province, :city, :county, :unitName, :affiliatedUnit, :purpose, :indoorAssetAvg, " +
                ":unitPrice1, :tel, :fillFormUser, :fillFormTime, :unitPrice2, :unitPrice31, :unitPrice32, " +
                ":unitPrice4, :unitPrice51, :unitPrice52, :unitPrice53, :unitPrice6, :unitPrice7, :area1, " +
                ":area2, :area31, :area32, :area4, :area51, :area52, :area53, :area6, :area7, :xlrmId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(govOffInfo);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<HouseCategoryArea> getDataByXsemId(String xsemId) {
        String sql = "select province,city,county,'政府办公' housePurposeName, " +
                "    SUM(COALESCE(CAST(NULLIF(area1, '') AS DECIMAL(10, 2)), 0)) AS v1Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area2, '') AS DECIMAL(10, 2)), 0)) AS v2Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area31, '') AS DECIMAL(10, 2)), 0)) AS v3Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area32, '') AS DECIMAL(10, 2)), 0)) AS v4Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area4, '') AS DECIMAL(10, 2)), 0)) AS v5Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area51, '') AS DECIMAL(10, 2)), 0)) AS v6Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area52, '') AS DECIMAL(10, 2)), 0)) AS v7Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area53, '') AS DECIMAL(10, 2)), 0)) AS v8Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area6, '') AS DECIMAL(10, 2)), 0)) AS v9Area, " +
                "    SUM(COALESCE(CAST(NULLIF(area7, '') AS DECIMAL(10, 2)), 0)) AS v10Area " +
                "from xczh_government_office_info a " +
                "left join xczh_loss_report_manage b on a.xlrm_id=b.id " +
                "where b.xsem_id= ?  " +
                "GROUP BY province,city,county,purpose";
        List<HouseCategoryArea> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseCategoryArea.class),xsemId);
        return list;
    }
}
